package org.yinxue.swing.sql.util;


import org.yinxue.swing.core.util.LogUtil;

import java.io.IOException;
import java.io.InputStream;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 提取SQL字段
 *
 * @author zengjian
 * @create 2018-03-11 11:31
 * @since 1.0.0
 */
public abstract class SQLUtil {

    static final String ERROR_MSG = "是不是输入错误了，啥都没有解析出来！";

    final static String LINE = System.getProperty("line.separator");
    static Set<String> excludeSet = new HashSet<>();
    static String[] excluedArray = {"key", "unique", "primary", "constraint"};
    // key = sqlFieldType value = javaFieldType
    static Map<String, String> sqlFieldMap = new HashMap<>();
    // key = javaFieldType value = sqlFieldType
    static Map<String, String> javaFieldMap = new HashMap<>();
    // key = javaFieldType value = num
    static Map<String, String> sqlFieldScope = new HashMap<>();

    static {
        excludeSet.addAll(Arrays.asList(excluedArray));
        // 读取sql字段类型与java字段类型的映射关系
        InputStream is = SQLUtil.class.getResourceAsStream("/sqlFieldMap");
        try {
            byte[] bytes = new byte[is.available()];
            is.read(bytes);
            String[] strArr = new String(bytes, "utf-8").split(LINE);
            for (String str : strArr) {
                if (!str.isEmpty()) {
                    String[] field = str.split(" {1,}"); //以一个或者以上空格分割
                    if (field.length >= 2 && field[0] != null && field[1] != null) {
                        sqlFieldMap.put(field[0], field[1]);
                        javaFieldMap.put(field[1], field[0]);
                    }
                    if (field.length >= 3) {
                        sqlFieldScope.put(field[1], field[2]);
                    }
                }
            }
        } catch (IOException e) {
            LogUtil.error(SQLUtil.class, "读取sqlFieldMap文件失败", e);
        }
    }

    /**
     * 驼峰处理，以下划线后一位修改为大写为标准
     *
     * @param sqlFieldList
     * @return
     */
    public static String toCamelCase(final String sqlFieldList) {
        // 1.小写处理
        String lowerStr = sqlFieldList.toLowerCase();
        StringBuilder builder = new StringBuilder(lowerStr);
        Pattern p = Pattern.compile("_");
        Matcher m = p.matcher(builder);
        int index = 0;
        while (m.find()) {
            // end = 匹配组0的最后一个字符的索引+1
            index = m.end();
            // 2.获得下划线后的首字母，并作大写处理
            char c = Character.toUpperCase(builder.charAt(index));
            builder.setCharAt(index, c);
        }
        // 3.替换下划线为""
        return builder.toString().replaceAll("_", "");
    }

    /**
     * 以大写字母为分隔，添加下划线
     *
     * @param sqlFieldList
     * @return
     */
    public static String toUnderLineCase(final String javaField) {
        checkStringNull(javaField);
        StringBuilder builder = new StringBuilder(javaField);
        Pattern p = Pattern.compile("[A-Z]");
        Matcher m = p.matcher(builder);
        int index = 0;
        StringBuffer buffer = new StringBuffer();
        while (m.find()) {
            m.appendReplacement(buffer, "_" + m.group());
        }
        m.appendTail(buffer);
        return buffer.toString();
    }

    public static String toFirstUpperCase(String str) {
        checkStringNull(str);
        return str.substring(0, 1).toUpperCase().concat(str.substring(1));
    }

    public static String toFirstLowerCase(String str) {
        checkStringNull(str);
        return str.substring(0, 1).toLowerCase().concat(str.substring(1));
    }


    /**
     * 提取SQL DDL语句中的表字段
     *
     * @param sql SQL DDL对象语句
     * @return 提取后的表字段
     */
    public static String extractSQLField(String sql) {
        checkStringNull(sql);
        List<String> list = extractSQLFieldAsList(sql);
        if (!list.isEmpty()) {
            return list.toString();
        }
        return extractSQLFieldAsList(sql).toString();
    }

    public static String extractSQLTableName(String sql) {
        checkStringNull(sql);
        // . 是可能存在带命名空间的情况，如DB2数据库表：UWPDB.SHOP_GOLD_BASIC_INFO
        Pattern pattern = Pattern.compile("TABLE\\s+`?([A-Za-z0-9_\\.]+)`?\\s+\\(");
        Matcher matcher = pattern.matcher(sql);
        if (matcher.find()) {
            return matcher.group(1);
        }
        return "?";
    }

    public static String extractJavaPojoTableName(String pojoCode) {
        checkStringNull(pojoCode);
        Pattern pattern = Pattern.compile("public\\s+class\\s+([A-Za-z]+)\\s+");
        Matcher matcher = pattern.matcher(pojoCode);
        if (matcher.find()) {
            return toUnderLineCase(toFirstLowerCase(matcher.group(1))).toUpperCase();
        }
        return "?";
    }

    /**
     * 提取SQL DDL语句中的表字段
     *
     * @param sql
     * @return
     */
    public static List<String> extractSQLFieldAsList(String sql) {
        checkStringNull(sql);
        // 以"("或者","+"\n"开头
        Pattern p = Pattern.compile("[\\(,]\\s+`?([A-Za-z_]+)`?");
        Matcher m = p.matcher(sql);
        List<String> fieldList = new StringList();
        String fieldName;
        while (m.find()) {
            fieldName = m.group(1);
            if (excludeSet.contains(fieldName.toLowerCase())) {
                continue;
            }
            fieldList.add(fieldName);
        }
        return fieldList;
    }

    public static StringLinkedMap extractSQLFieldAndTypeAsMap(String sql) {
        checkStringNull(sql);
        // 取得字段中的名称及类型
        StringLinkedMap fieldTypeMap = new StringLinkedMap();
        Pattern pattern = Pattern.compile("[\\(,]\\s+`?([A-Za-z_]+)`? +([a-zA-Z]+)");
        Matcher matcher = pattern.matcher(sql);
        while (matcher.find()) {
            if (excludeSet.contains(matcher.group(1).toLowerCase())) {
                continue;
            }
            fieldTypeMap.put(matcher.group(1), matcher.group(2));
        }
        if (!fieldTypeMap.isEmpty()) {
            return fieldTypeMap;
        }
        throw new RuntimeException(ERROR_MSG);
    }


    /**
     * 拼接实体字段
     *
     * @param sql
     * @return
     */
    public static String createEntityField(String sql) {
        checkStringNull(sql);
        StringLinkedMap linkedHashMap = extractSQLFieldAndTypeAsMap(sql);
        // 拼接构成实体类
        StringBuilder builder = new StringBuilder();
        for (Map.Entry<String, String> entry : linkedHashMap.entrySet()) {
            String sqlFieldName = entry.getKey();  // 字段
            String sqlFieldType = entry.getValue(); // 字段类型
            String javaFieldType = getJavaType(sqlFieldType);
            String javaFieldName = toCamelCase(sqlFieldName);
            builder.append("private ").append(javaFieldType).append(" ").append(javaFieldName).append(";").append(LINE);
        }
        return builder.toString();
    }

    public static String createSQLDDL(String pojoCode) {
        checkStringNull(pojoCode);
        // key 字段名  value 字段类型
        StringLinkedMap map = extractJavaFieldAndTypeAsMap(pojoCode);
        StringBuilder builder = new StringBuilder("CREATE TABLE ");
        builder.append("`").append(extractJavaPojoTableName(pojoCode)).append("` (\n");
        Iterator<Map.Entry<String, String>> iterator = map.entrySet().iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, String> entry = iterator.next();
            String fieldName = entry.getKey();
            String fieldType = entry.getValue();
            String sqlType = javaFieldMap.get(fieldType);
            // 未命中的都用varchar
            sqlType = sqlType == null ? "varchar" : sqlType;
            builder.append("    ").append("`").append(toUnderLineCase(fieldName).toUpperCase()).append("` ");
            builder.append(sqlType.toUpperCase());
            if (!"0".equals(sqlFieldScope.get(fieldType))) {
                builder.append("(").append(sqlFieldScope.get(fieldType)).append(")");
            }
            if (iterator.hasNext()) {
                builder.append(",");
            }
            builder.append("\n");
        }
        builder.append("  ) ENGINE=InnoDB AUTO_INCREMENT=184 DEFAULT CHARSET=utf8");
        return builder.toString();
    }

    /**
     * key = sql字段
     * value = java字段
     *
     * @param sql
     * @return
     */
    private static StringLinkedMap convertToSQLJavaFieldMap(String sql) {
        StringLinkedMap stringLinkedMap = extractSQLFieldAndTypeAsMap(sql);
        // 将key进行驼峰转换
        StringLinkedMap map = new StringLinkedMap();
        for (Map.Entry<String, String> entry : stringLinkedMap.entrySet()) {
            map.put(toCamelCase(entry.getKey()), entry.getKey());
        }
        if (!map.isEmpty()) {
            return map;
        }
        throw new RuntimeException(ERROR_MSG);
    }

    private static String getJavaType(String sqlFieldType) {
        String javaType = sqlFieldMap.get(sqlFieldType.toLowerCase());
        return javaType == null ? "String" : javaType;
    }


    public static String convertToQuery(String sql) {
        checkStringNull(sql);
        LinkedHashMap<String, String> linkedHashMap = convertToSQLJavaFieldMap(sql);
        StringBuilder builder1 = new StringBuilder(150);
        builder1.append("SELECT\n");
        Iterator<Map.Entry<String, String>> iterator = linkedHashMap.entrySet().iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, String> entry = iterator.next();
            builder1.append("    ").append(entry.getValue());
            if (iterator.hasNext()) {
                builder1.append(",\n");
            }
        }
        builder1.append("\n  FROM\n    ").append(extractSQLTableName(sql))
                .append("\n  WHERE \n    ? = ?\n  ORDER BY\n    ?\n  LIMIT\n    ?, ?");
        return builder1.toString();
    }

    private static void checkStringNull(String str) {
        if (str == null || str.length() == 0 || str.trim().equals("")) {
            Exception exception = new Exception();
            StackTraceElement[] stackTrace = exception.getStackTrace();
            if (stackTrace.length <= 1) {
                throw new Error("jvm调用错误");
            }
            String methodName = stackTrace[1].getMethodName();
            String fileName = stackTrace[1].getFileName();
            throw new RuntimeException(fileName + "的方法:" + methodName + "String入参不能为空");
        }
    }

    public static String convertToInsert(String sql) {
        checkStringNull(sql);
        StringLinkedMap stringLinkedMap = convertToSQLJavaFieldMap(sql);
        StringBuilder builder1 = new StringBuilder(150);
        StringBuilder builder2 = new StringBuilder(150);
        builder1.append("INSERT INTO\n").append("  ").append(extractSQLTableName(sql)).append(" (\n");
        builder2.append("  VALUES (\n");
        Iterator<Map.Entry<String, String>> iterator = stringLinkedMap.entrySet().iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, String> entry = iterator.next();
            // 排除ID字段
            if ("id".equals(entry.getValue().toLowerCase())) {
                continue;
            }
            builder1.append("    ").append(entry.getValue());
            builder2.append("    :").append(entry.getKey());
            if (iterator.hasNext()) {
                builder1.append(",\n");
                builder2.append(",\n");
            }
        }
        builder1.append("\n  )").append(builder2).append("\n  )");
        return builder1.toString();
    }


    public static String convertToDelete(String sql) {
        checkStringNull(sql);
        StringBuilder builder = new StringBuilder(100);
        builder.append("DELETE FROM\n    ").append(extractSQLTableName(sql)).append("\n  WHERE\n    ?=?");
        return builder.toString();
    }

    public static String convertToUpdate(String sql) {
        checkStringNull(sql);
        LinkedHashMap<String, String> linkedHashMap = convertToSQLJavaFieldMap(sql);
        StringBuilder builder = new StringBuilder(150);
        builder.append("UPDATE\n    ").append(extractSQLTableName(sql)).append("\n  SET\n");
        Iterator<Map.Entry<String, String>> iterator = linkedHashMap.entrySet().iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, String> entry = iterator.next();
            // 排除ID字段
            if ("id".equals(entry.getValue().toLowerCase())) {
                continue;
            }
            builder.append("    ").append(entry.getValue()).append(" = :").append(entry.getKey());
            if (iterator.hasNext()) {
                builder.append(",").append("\n");
            }
        }
        builder.append("\n  WHERE\n    ? = ?");
        return builder.toString();
    }


    public static String convertToQueryForMybatis(String sql) {
        checkStringNull(sql);
        LinkedHashMap<String, String> linkedHashMap = convertToSQLJavaFieldMap(sql);
        StringBuilder builder1 = new StringBuilder(150);
        builder1.append("SELECT\n");
        Iterator<Map.Entry<String, String>> iterator = linkedHashMap.entrySet().iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, String> entry = iterator.next();
            builder1.append("    ").append(entry.getValue());
            if (iterator.hasNext()) {
                builder1.append(",\n");
            }
        }
        builder1.append("\n  FROM\n    ").append(extractSQLTableName(sql))
                .append("\n  WHERE \n    ? = #{?}\n  ORDER BY\n    ${?}  ${?}\n  LIMIT\n    #{?}, #{?}");
        return builder1.toString();
    }


    public static String convertToInsertForMybatis(String sql) {
        checkStringNull(sql);
        LinkedHashMap<String, String> linkedHashMap = convertToSQLJavaFieldMap(sql);
        StringBuilder builder1 = new StringBuilder(128);
        StringBuilder builder2 = new StringBuilder(128);
        builder1.append("INSERT INTO\n  ").append(extractSQLTableName(sql)).append(" (\n");
        builder2.append("\n  ) VALUES (\n");
        Iterator<Map.Entry<String, String>> iterator = linkedHashMap.entrySet().iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, String> entry = iterator.next();
            // 排除ID字段
            if ("id".equals(entry.getValue().toLowerCase())) {
                continue;
            }
            builder1.append("    ").append(entry.getValue());
            builder2.append("    ").append("#{").append(entry.getKey()).append("}");
            if (iterator.hasNext()) {
                builder1.append(",\n");
                builder2.append(",\n");
            }
        }
        builder1.append(builder2).append("\n  )");
        return builder1.toString();
    }

    public static String convertToUpdateForMybatis(String sql) {
        checkStringNull(sql);
        LinkedHashMap<String, String> linkedHashMap = convertToSQLJavaFieldMap(sql);
        StringBuilder builder = new StringBuilder(256);
        builder.append("UPDATE\n    ").append(extractSQLTableName(sql)).append("\n  SET\n");
        Iterator<Map.Entry<String, String>> iterator = linkedHashMap.entrySet().iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, String> entry = iterator.next();
            // 排除ID字段
            if ("id".equals(entry.getValue().toLowerCase())) {
                continue;
            }
            builder.append("    ").append(entry.getValue()).append(" = #{").append(entry.getKey()).append("}");
            if (iterator.hasNext()) {
                builder.append(",").append("\n");
            }
        }
        builder.append("\n  WHERE\n    ? = #{?}");
        return builder.toString();
    }

    public static String convertToDeleteForMybatis(String sql) {
        checkStringNull(sql);
        StringBuilder sb = new StringBuilder(128);
        sb.append("DELETE FROM\n    ").append(extractSQLTableName(sql)).append("\n  WHERE\n    ? = #{?}");
        return sb.toString();
    }


    public static String convertToQueryForPs(String sql) {
        checkStringNull(sql);
        LinkedHashMap<String, String> linkedHashMap = convertToSQLJavaFieldMap(sql);
        StringBuilder builder1 = new StringBuilder(150);
        builder1.append("SELECT\n");
        Iterator<Map.Entry<String, String>> iterator = linkedHashMap.entrySet().iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, String> entry = iterator.next();
            builder1.append("    ").append(entry.getValue());
            if (iterator.hasNext()) {
                builder1.append(",\n");
            }
        }
        builder1.append("\n  FROM\n    ").append(extractSQLTableName(sql))
                .append("\n  WHERE \n    字段名 = ?\n  ORDER BY\n    ?  ?\n  LIMIT\n    ?, ?");
        return builder1.toString();
    }

    public static String convertToInsertForPs(String sql) {
        checkStringNull(sql);
        LinkedHashMap<String, String> linkedHashMap = convertToSQLJavaFieldMap(sql);
        StringBuilder builder1 = new StringBuilder(128);
        StringBuilder builder2 = new StringBuilder(128);
        builder1.append("INSERT INTO\n  ").append(extractSQLTableName(sql)).append(" (\n");
        builder2.append("\n  ) VALUES (\n");
        Iterator<Map.Entry<String, String>> iterator = linkedHashMap.entrySet().iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, String> entry = iterator.next();
            // 排除ID字段
            if ("id".equals(entry.getValue().toLowerCase())) {
                continue;
            }
            builder1.append("    ").append(entry.getValue());
            builder2.append("    ").append("?");
            if (iterator.hasNext()) {
                builder1.append(",\n");
                builder2.append(",\n");
            }
        }
        builder1.append(builder2).append("\n  )");
        return builder1.toString();
    }

    public static String convertToUpdateForPs(String sql) {
        checkStringNull(sql);
        LinkedHashMap<String, String> linkedHashMap = convertToSQLJavaFieldMap(sql);
        StringBuilder builder = new StringBuilder(256);
        builder.append("UPDATE\n    ").append(extractSQLTableName(sql)).append("\n  SET\n");
        Iterator<Map.Entry<String, String>> iterator = linkedHashMap.entrySet().iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, String> entry = iterator.next();
            // 排除ID字段
            if ("id".equals(entry.getValue().toLowerCase())) {
                continue;
            }
            builder.append("    ").append(entry.getValue()).append(" = ?");
            if (iterator.hasNext()) {
                builder.append(",").append("\n");
            }
        }
        builder.append("\n  WHERE\n    字段名 = ?");
        return builder.toString();
    }

    public static String convertToDeleteForPs(String sql) {
        checkStringNull(sql);
        StringBuilder sb = new StringBuilder(128);
        sb.append("DELETE FROM\n    ").append(extractSQLTableName(sql)).append("\n  WHERE\n    字段名 = ?");
        return sb.toString();
    }

    public static List<String> extractJavaField(String pojoCode) {
        checkStringNull(pojoCode);
        Pattern pattern = Pattern.compile("\\s+private\\s+[A-Za-z]+\\s+([A-Za-z]+);");
        Matcher matcher = pattern.matcher(pojoCode);
        List<String> stringList = new StringList(32);
        while (matcher.find()) {
            stringList.add(matcher.group(1));
        }
        if (!stringList.isEmpty()) {
            return stringList;
        }
        throw new RuntimeException(ERROR_MSG);
    }

    public static StringLinkedMap extractJavaFieldAndTypeAsMap(String pojoCode) {
        checkStringNull(pojoCode);
        // 取得字段中的名称及类型
        StringLinkedMap fieldTypeMap = new StringLinkedMap();
        Pattern pattern = Pattern.compile("\\s+private\\s+([A-Za-z]+)\\s+([A-Za-z]+);");
        Matcher matcher = pattern.matcher(pojoCode);
        while (matcher.find()) {
            fieldTypeMap.put(matcher.group(2), matcher.group(1));
        }
        if (!fieldTypeMap.isEmpty()) {
            return fieldTypeMap;
        }
        throw new RuntimeException(ERROR_MSG);
    }


    /**
     * 主要检查的内容<br>
     * 1. 查询语句
     * 1.1 是否少逗号
     * 1.2 是否大写
     * 1.3 是否带条件where查询
     * 1.4 是否查询指定字段(即查是否是带*号查询了)
     * 1.5 拼写检查
     * 2. 新增语句
     * 3. 更新语句
     * 4. 删除语句
     *
     * @param text
     * @return
     */
    public static String checkSQLCorrect(String text) {
        return "第几行第几个单词有问题";
    }

    public static void main(String[] args) {
        convertToQuery("");
    }

}
